package org.beetl.sql.core.db;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Map;
import java.util.concurrent.ConcurrentHashMap;

import org.beetl.sql.core.BeetlSQLException;
import org.beetl.sql.core.ConnectionSource;
import org.beetl.sql.core.SQLManager;
import org.beetl.support.CommonSupport;
import org.beetl.support.LogSupport;

public class MetadataManager {

 private ConnectionSource ds = null;
 Map<String, TableDesc> map = null;
 TableDesc NOT_EXIST = new TableDesc("$NOT_EXIST", "");
 SQLManager sm = null;

 public MetadataManager(ConnectionSource ds, SQLManager sm) {
  super();
  this.ds = ds;
  this.sm = sm;
 }

 public ConnectionSource getDs() {
  return ds;
 }

 public void setDs(ConnectionSource ds) {
  this.ds = ds;
 }

 /***
  * 表是否在数据库中
  * 
  * @param tableName
  * @return
  */
 public boolean existTable(String tableName) {
  TableDesc t = getTable(tableName);
  return t != null;
 }

 public TableDesc getTable(String name) {
  TableDesc table = getTableFromMap(name);
  if (table.getMetaCols().size() == 0) {
   table = initTable(name);
  }
  if (table == NOT_EXIST) {
   throw new BeetlSQLException(BeetlSQLException.TABLE_NOT_EXIST, "table \"" + name + "\" not exist");
  }
  return table;
 }

 private TableDesc getTableFromMap(String tableName) {
  String name = tableName.toUpperCase();
  if (map == null) {
   synchronized (this) {
    if (map != null)
     return map.get(name);
    this.initMetadata();
   }
  }
  return map.get(name);
 }

 private TableDesc initTable(String tableName) {
  TableDesc desc = this.getTableFromMap(tableName);
  if (desc.getMetaCols().size() != 0) {
   return desc;
  }
  synchronized (desc) {
   if (desc.getMetaCols().size() != 0) {
    return desc;
   }
   Connection conn = null;
   ResultSet rs = null;
   try {
    conn = ds.getMaster();
    DatabaseMetaData dbmd = conn.getMetaData();
    rs = dbmd.getPrimaryKeys(null, "%", desc.getMetaName());
    int count = 0;
    while (rs.next()) {
     count++;
     String metaIdName = rs.getString("COLUMN_NAME");

     desc.setIdName(metaIdName.toUpperCase());
    }

    // 多个主键 下个版本再做
    if (count > 1)
     throw new BeetlSQLException(BeetlSQLException.ID_EXPECTED_ONE_ERROR);

    rs = dbmd.getColumns(null, "%", desc.getMetaName(), "%");
    while (rs.next()) {
     String colName = rs.getString("COLUMN_NAME");
     Integer sqlType = rs.getInt("DATA_TYPE");
     Integer size = rs.getInt("COLUMN_SIZE");
     Object o = rs.getObject("DECIMAL_DIGITS");
     Integer digit = null;
     if (o != null) {
      digit = ((Number) o).intValue();
     }

     String remark = rs.getString("REMARKS");
     ColDesc col = new ColDesc(colName, sqlType, size, digit, remark);
     desc.addCols(col);
    }
    rs.close();
    this.afterInitTable(desc, dbmd, conn);
    return desc;
   } catch (SQLException e) {
    throw new BeetlSQLException(BeetlSQLException.SQL_EXCEPTION, e);
   } finally {
    close(conn);
   }
  }

 }

 /**
  * 
  * @param desc
  * @param dbmd
  * @param conn
  */
 public void afterInitTable(TableDesc desc, DatabaseMetaData dbmd, Connection conn) {
  try {
   String dbProductName = dbmd.getDatabaseProductName();
   LogSupport.logger.info("after init table for database {}", dbProductName);
   if ("oracle".equalsIgnoreCase(dbProductName)) {
    afterInitTableForOracle(desc, dbmd, conn);
   }
  } catch (SQLException e) {
   LogSupport.logger.error("{}", e);
  }

 }

 public void afterInitTableForOracle(TableDesc desc, DatabaseMetaData dbmd, Connection conn) throws SQLException {
  // 表注释
  if (CommonSupport.isBlank(desc.getRemark())) {
   String tableRemarkSql = "select TABLE_NAME \"name\",COMMENTS \"remark\" from all_tab_comments where table_name=?";
   PreparedStatement tableRemarkStmt = conn.prepareStatement(tableRemarkSql);
   tableRemarkStmt.setString(1, desc.getName());
   ResultSet rs = tableRemarkStmt.executeQuery();
   while (rs.next()) {
    desc.setRemark(rs.getString("remark"));
    LogSupport.logger.info("update tableDesc remark {}->{}", desc.getName(), desc.getRemark());
   }
   rs.close();
  }
  // 列注释
  String sqlColRemark = "SELECT a.column_name \"name\",b.comments \"remark\" FROM all_tab_columns a,all_col_comments b WHERE a.owner=b.owner AND a.table_name=b.table_name AND a.column_name=b.column_name AND a.table_name =?";
  PreparedStatement psColRemark = conn.prepareStatement(sqlColRemark);
  psColRemark.setString(1, desc.getName());
  ResultSet rs = psColRemark.executeQuery();
  while (rs.next()) {
   String name = rs.getString("name");
   String remark = rs.getString("remark");
   if (CommonSupport.isNotBlank(remark)) {
    ColDesc colDesc = desc.getColDesc(name);
    if (colDesc != null && CommonSupport.isBlank(colDesc.remark)) {
     colDesc.remark = remark;
     LogSupport.logger.info("update colDesc remark {}.{}->{}", desc.getName(), name, remark);
    }
   }
  }
  rs.close();

 }

 private synchronized void initMetadata() {
  if (map != null)
   return;
  map = new ConcurrentHashMap<String, TableDesc>();
  Connection conn = null;
  try {
   conn = ds.getMaster();
   DatabaseMetaData dbmd = conn.getMetaData();

   ResultSet rs = dbmd.getTables(null, "%", null, new String[] { "TABLE" });
   while (rs.next()) {
    String name = rs.getString("TABLE_NAME");
    String remarks = rs.getString("REMARKS");
    // System.out.println("remarks="+remarks);
    TableDesc desc = new TableDesc(name, remarks);
    map.put(desc.getName(), desc);
   }

  } catch (SQLException e) {
   throw new BeetlSQLException(BeetlSQLException.SQL_EXCEPTION, e);
  } finally {
   close(conn);
  }
 }

 private void close(Connection conn) {
  try {
   if (!ds.isTransaction()) {
    conn.close();
   }
  } catch (Exception ex) {
   ex.printStackTrace();
  }

 }
}
